//使用Express框架搭建服务器
const express = require('express');
//引入mysql模块
const mysql1 = require('mysql');
// 跨越访问
const path = require('path');
const fs = require('fs');
const cors = require('cors');
//引入body-parser模块
const bodyParser = require('body-parser');
const formidable = require("formidable")
//使用body-parser中间件
const app = express();
// 使用upload文件夹作为静态文件
app.use(express.static('upload'));
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));
//创建连接池
const pool = mysql1.createPool({
    host: '192.168.224.76',
    user: 'root',
    password: '123',
    database: 'studentdb'
});
// 跨越访问
app.use(cors());
pool.on('connection', function (connection) {
    console.log('Connection Id: ' + connection.threadId);
});
// 获取学生信息的接口,带分页page,pageSize
app.get('/api/getStudent', (req, res) => {
    var { page, pageSize } = req.query;
    console.log(page, pageSize);
    pool.query('SELECT * FROM student order by sid asc limit ?,?', [(page - 1) * pageSize, parseInt(pageSize)], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error getting student');
        } else {
            res.json(result);
        }
    });
});
// 获取学生总数的接口
app.get('/api/getStudentCount', (req, res) => {
    pool.query('SELECT COUNT(*) as count FROM student', (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error getting student count');
        } else {
            res.json(result);
        }
    });
});
// 添加学生信息的接口
app.post('/api/addStudent', (req, res) => {
    var { sid, sname, gender, telphone, address } = req.body;
    pool.query('INSERT INTO student (sid,sname,gender,telphone,address,createTime) VALUES (?,?,?,?,?,NOW())', [sid, sname, gender, telphone, address], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error adding student');
        } else {
            res.json(result);
        }
    });
});
// 修改学生信息的接口
app.post('/api/updateStudent', (req, res) => {
    var { sid, sname, gender, telphone, address } = req.body;
    pool.query('UPDATE student SET sname=?,gender=?,telphone=?,address=?,createTime=NOW() WHERE sid=?', [sname, gender, telphone, address, sid], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error updating student');
        } else {
            res.json(result);
        }
    });
});
// 删除学生信息的接口
app.post('/api/deleteStudent', (req, res) => {
    var { sid } = req.body;
    pool.query('DELETE FROM student WHERE sid=?', [sid], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error deleting student');
        } else {
            res.json(result);
        }
    });
});
//用户登录接口
app.post('/api/login', (req, res) => {
    var { sid, password } = req.body;
    pool.query('SELECT * FROM user WHERE sid=? AND password=md5(?)', [sid, password], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error logging in');
        } else {
            // res.json(result);
            if (result.length > 0) {
                res.json({ code: 200, msg: '登录成功', data: result });
            } else {
                res.json({ code: 400, msg: '登录失败', data: result });
            }
        }
    });
});
// 用户注册接口
app.post('/api/register', (req, res) => {
    var { sid, password, role } = req.body;
    pool.query('INSERT INTO user (sid,password,role,lastloginTime) VALUES (?,md5(?),?,NOW())', [sid, password, role], (err, result) => {
        if (err) {
            console.log(err);
            res.json({ code: 400, msg: '注册失败', data: err });
        } else {
            res.json({ code: 200, msg: '注册成功', data: result });
        }
    });
});
//检查用户是否存在接口
app.post('/api/check', (req, res) => {
    var { sid } = req.body;
    pool.query('SELECT * FROM user WHERE sid=?', [sid], (err, result) => {
        if (err) {
            console.log(err);
            res.json({ code: 400, msg: '检查用户是否存在失败', data: err });
        } else {
            if (result.length > 0) {
                res.json({ code: 200, msg: '用户已存在', data: result });
            } else {
                res.json({ code: 400, msg: '用户不存在', data: result });
            }
        }
    });
});
//对性别进行统计的接口
app.get('/api/getGenderCount', (req, res) => {
    pool.query('SELECT gender,COUNT(*) as count FROM student GROUP BY gender', (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error getting gender count');
        } else {
            res.json({ code: 200, msg: '获取性别统计成功', data: result })
        }
    });
});
// 更换头像
app.post('/api/changeAvatar', (req, res) => {
    var { sid, avatar } = req.body;//获取sid和avatar 这两个参数 upload/*.jpg
    pool.query('UPDATE user SET avatar=? WHERE sid=?', [avatar, sid], (err, result) => {
        if (err) {
            console.log(err);
            res.status(500).send('Error changing avatar');
        } else {
            res.json({ code: 200, msg: '更换头像成功', data: result });
        }
    });
});
// 接受图片上传的接口
app.post('/api/upload', (req, res) => {
 
    const form = new formidable.IncomingForm();
    form.uploadDir =path.resolve(__dirname,"upload/"); // 上传图片放置的文件夹
    console.log("--"+form.uploadDir);
    form.options.keepExtensions = true; // 保持文件的原始扩展名
    form.parse(req, (err, fields, files) => {
        console.log(fields);
        var { sid } = fields;
        console.log(sid);
        const oldFileName = files.imageFile[0].originalFilename;
        const newFileName = new Date().getTime()+path.extname(oldFileName);
        const newFilePath = path.resolve(__dirname,"upload/",newFileName);
        fs.renameSync(files.imageFile[0].filepath,newFilePath);
        pool.query('UPDATE user SET avatar="/upload/"? WHERE sid=?', [newFileName, sid], (err, result) => {
            if (err) {
                console.log(err);
                res.json({ code: 400, success: false, message: '更换头像失败', data: err });
            } else {
                res.json({ code: 200, success: true, message: '更换头像成功', data: result });
            }
        });
       
    });
});


    //监听端口
    app.listen(3000, () => {
        console.log('Server is running on port 3000');
    });
